<?php

require_once 'Categories.php';
require_once 'Users.php';

class Items {

    private $_ID;
    private $_code;
    private $_alias;
    private $_name;
    private $_description;
    private $_detail;
    private $_image;
    private $_categoryID;
    private $_price;
    private $_createdDate;
    private $_modifiedDate;
    private $_createdUserID;
    private $_modifiedUserID;
    private $_order;
    private $_state;
    // các đối tượng
    private $_category;
    private $_createdUser;
    private $_modifiedUser;

    public function getCreatedUserID() {
        return $this->_createdUserID;
    }

    public function setCreatedUserID($createdUserID) {
        $this->_createdUserID = $createdUserID;
    }

    public function getModifiedUserID() {
        return $this->_modifiedUserID;
    }

    public function setModifiedUserID($modifiedUserID) {
        $this->_modifiedUserID = $modifiedUserID;
    }

    public function getState() {
        return $this->_state;
    }

    public function setState($state) {
        $this->_state = $state;
    }

    public function getID() {
        return $this->_ID;
    }

    public function setID($ID) {
        $this->_ID = $ID;
    }

    public function getCode() {
        return $this->_code;
    }

    public function setCode($code) {
        $this->_code = $code;
    }

    public function getAlias() {
        return $this->_alias;
    }

    public function setAlias($alias) {
        $this->_alias = $alias;
    }

    public function getName() {
        return $this->_name;
    }

    public function setName($name) {
        $this->_name = $name;
    }

    public function getDescription() {
        return $this->_description;
    }

    public function setDescription($description) {
        $this->_description = $description;
    }

    public function getDetail() {
        return $this->_detail;
    }

    public function setDetail($detail) {
        $this->_detail = $detail;
    }

    public function getImage() {
        return $this->_image;
    }

    public function setImage($image) {
        $this->_image = $image;
    }

    public function getCategoryID() {
        return $this->_categoryID;
    }

    public function setCategoryID($categoryID) {
        $this->_categoryID = $categoryID;
    }

    public function getPrice() {
        return $this->_price;
    }

    public function setPrice($price) {
        $this->_price = $price;
    }

    public function getCreatedDate() {
        return $this->_createdDate;
    }

    public function setCreatedDate($createdDate) {
        $this->_createdDate = $createdDate;
    }

    public function getModifiedDate() {
        return $this->_modifiedDate;
    }

    public function setModifiedDate($modifiedDate) {
        $this->_modifiedDate = $modifiedDate;
    }

    public function getCreatedUser() {
        return $this->_createdUser;
    }

    public function setCreatedUser($createdUser) {
        $this->_createdUser = $createdUser;
    }

    public function getModifiedUser() {
        return $this->_modifiedUser;
    }

    public function setModifiedUser($modifiedUser) {
        $this->_modifiedUser = $modifiedUser;
    }

    public function getOrder() {
        return $this->_order;
    }

    public function setOrder($order) {
        $this->_order = $order;
    }

    public function getCategory() {
        return $this->_category;
    }

    public function setCategory($category) {
        $this->_category = $category;
    }

    // tải danh sách bài viết theo mã chủ đề
    public function getListByCategoryID($db) {
        if ($db != null) {
            $sql = "SELECT 
                    `I_ID`,
                    `I_Code`,
                    `I_Alias`,
                    `I_Title`,
                    `I_Description`,
                    `I_Detail`,
                    `I_Image`,
                    `I_CategoryID`,
                    `I_Price`,
                    I_CreatedDate,
                    I_ModifiedDate, 
                    `I_CreatedUser`,
                    `I_ModifiedUser`,
                    `I_State`,
                    `I_Order`
                    FROM items 
                    WHERE I_CategoryID IN (                       
        		SELECT  DISTINCT level1.C_ID        
        		FROM categories AS level1  
                        
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID 
                        
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID    
                        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID   
                        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID  
                        
        		WHERE level1.C_ID = :I_CategoryID AND level1.C_ID <>-1    
                    
        		UNION
        
        		SELECT  DISTINCT level2.C_ID        
        		FROM categories AS level1    
                        
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID      
                        
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID  
                        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID 
                        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID  
                        
        		WHERE level1.C_ID = :I_CategoryID AND level2.C_ID <>-1
        
        		UNION 
        
        		SELECT DISTINCT level3.C_ID        
        		FROM categories AS level1    
                        
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID   
                        
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID   
                        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID   
                        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID  
                        
        		WHERE level1.C_ID = :I_CategoryID AND level3.C_ID <>-1
        
        		UNION 
        
        		SELECT DISTINCT level4.C_ID        
        		FROM categories AS level1   
                        
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID  
                        
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID  
                        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID    
                        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID    
                        
        		WHERE level1.C_ID = :I_CategoryID AND level4.C_ID <>-1
        
        		UNION 
        
        		SELECT DISTINCT level5.C_ID	        
        		FROM categories AS level1         
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID         
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID        
        		WHERE level1.C_ID = :I_CategoryID AND level5.C_ID <>-1    
        	)
        	ORDER BY I_Order DESC";

            $parameters = array(':I_CategoryID');
            $values = array($this->getCategoryID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $item = new Items();
                    $item->setID($row->I_ID);
                    $item->setCode($row->I_Code);
                    $item->setAlias($row->I_Alias);
                    $item->setName($row->I_Title);
                    $item->setDescription($row->I_Description);
                    $item->setDetail($row->I_Detail);
                    $item->setImage($row->I_Image);
                    $item->setCategoryID($row->I_CategoryID);
                    $item->setPrice($row->I_Price);
                    $item->setCreatedDate($row->I_CreatedDate);
                    $item->setModifiedDate($row->I_ModifiedDate);
                    $item->setCreatedUserID($row->I_CreatedUser);
                    $item->setModifiedUserID($row->I_ModifiedUser);
                    $item->setState($row->I_State);
                    $item->setOrder($row->I_Order);

                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($item->getCreatedUserID());
                    $createdUser->getByID($db);
                    $item->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($item->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $item->setModifiedUser($modifiedUser);


                    // category 
                    $category = new Categories();
                    $category->setID($item->getCategoryID());
                    $category = $category->getByID($db);
                    $item->setCategory($category);


                    $rows[] = $item;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // tải danh sách bài viết theo mã chủ đề
    public function getList($db) {
        if ($db != null) {
            $sql = "SELECT 
                    `I_ID`,
                    `I_Code`,
                    `I_Alias`,
                    `I_Title`,
                    `I_Description`,
                    `I_Detail`,
                    `I_Image`,
                    `I_CategoryID`,
                    `I_Price`,
                    I_CreatedDate,
                    I_ModifiedDate, 
                    `I_CreatedUser`,
                    `I_ModifiedUser`,
                    `I_State`,
                    `I_Order`
                    FROM items
                    ORDER BY I_Order DESC";

            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $item = new Items();
                    $item->setID($row->I_ID);
                    $item->setCode($row->I_Code);
                    $item->setAlias($row->I_Alias);
                    $item->setName($row->I_Title);
                    $item->setDescription($row->I_Description);
                    $item->setDetail($row->I_Detail);
                    $item->setImage($row->I_Image);
                    $item->setCategoryID($row->I_CategoryID);
                    $item->setPrice($row->I_Price);
                    $item->setCreatedDate($row->I_CreatedDate);
                    $item->setModifiedDate($row->I_ModifiedDate);
                    $item->setCreatedUserID($row->I_CreatedUser);
                    $item->setModifiedUserID($row->I_ModifiedUser);
                    $item->setState($row->I_State);
                    $item->setOrder($row->I_Order);

                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($item->getCreatedUserID());
                    $createdUser->getByID($db);
                    $item->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($item->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $item->setModifiedUser($modifiedUser);


                    // category 
                    $category = new Categories();
                    $category->setID($item->getCategoryID());
                    $category = $category->getByID($db);
                    $item->setCategory($category);

                    $rows[] = $item;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // tải danh sách bài viết theo mã chủ đề
    public function search($db, $propertyValue) {
        if ($db != null) {

            $sql = 'SELECT 
                    `I_ID`,
                    `I_Code`,
                    `I_Alias`,
                    `I_Title`,
                    `I_Description`,
                    `I_Detail`,
                    `I_Image`,
                    `I_CategoryID`,
                    `I_Price`,
                    I_CreatedDate,
                    I_ModifiedDate, 
                    `I_CreatedUser`,
                    `I_ModifiedUser`,
                    `I_State`,
                    `I_Order`
                    FROM items 
                    LEFT JOIN `item_properties` ON `IP_I_ID` = `I_ID`
                    WHERE 1=1';

            $parameters = array();
            $values = array();
            $types = array();


            // nếu có tiêu đề
            if ($this->getName() != '') {

                $sql .= " AND I_Title LIKE :I_Title";

                $name = $this->getName();

                $parameters[] = ':I_Title';
                $values[] = "%$name%";
                $types[] = PDO::PARAM_STR;
            }

            // nếu có giá
            if ($this->getPrice() != '') {

                $price = explode('-', $this->getPrice());

                $sql .= " AND I_Price BETWEEN :value1 AND :value2";

                $parameters[] = ':value1';
                $values[] = $price[0];
                $types[] = PDO::PARAM_INT;

                $parameters[] = ':value2';
                $values[] = $price[1];
                $types[] = PDO::PARAM_INT;
            }
            // nếu có thuộc tính
            if ($propertyValue != '') {
                $sql .= " AND `IP_Value` = :IP_Value";

                $parameters[] = ':IP_Value';
                $values[] = $propertyValue;
                $types[] = PDO::PARAM_STR;
            }


            $sql.=' ORDER BY I_Order DESC';

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $item = new Items();
                    $item->setID($row->I_ID);
                    $item->setCode($row->I_Code);
                    $item->setAlias($row->I_Alias);
                    $item->setName($row->I_Title);
                    $item->setDescription($row->I_Description);
                    $item->setDetail($row->I_Detail);
                    $item->setImage($row->I_Image);
                    $item->setCategoryID($row->I_CategoryID);
                    $item->setPrice($row->I_Price);
                    $item->setCreatedDate($row->I_CreatedDate);
                    $item->setModifiedDate($row->I_ModifiedDate);
                    $item->setCreatedUserID($row->I_CreatedUser);
                    $item->setModifiedUserID($row->I_ModifiedUser);
                    $item->setState($row->I_State);
                    $item->setOrder($row->I_Order);

                    // created user
//                    $createdUser = new Users();
//                    $createdUser->setID($item->getCreatedUserID());
//                    $createdUser->getByID($db);
//                    $item->setCreatedUser($createdUser);
                    // modified user
//                    $modifiedUser = new Users();
//                    $modifiedUser->setID($item->getModifiedUserID());
//                    $modifiedUser->getByID($db);
//                    $item->setModifiedUser($modifiedUser);
                    // category 
//                    $category = new Categories();
//                    $category->setID($item->getCategoryID());
//                    $category = $category->getByID($db);
//                    $item->setCategory($category);

                    $rows[] = $item;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    public function getListOfTheSameCategory($db) {
        if ($db != null) {
            $sql = "SELECT 
                    `I_ID`,
                    `I_Code`,
                    `I_Alias`,
                    `I_Title`,
                    `I_Description`,
                    `I_Detail`,
                    `I_Image`,
                    `I_CategoryID`,
                    `I_Price`,
                    Date_FORMAT(I_CreatedDate, '%d/%m/%Y') As I_CreatedDate,
                    Date_FORMAT(I_ModifiedDate, '%d/%m/%Y') As I_ModifiedDate, 
                    `I_CreatedUser`,
                    `I_ModifiedUser`,
                    `I_State`,
                    `I_Order`
                    FROM items
                    WHERE I_Order < :I_Order
                    AND I_CategoryID = :I_CategoryID
                    ORDER BY I_Order DESC
                    LIMIT 10";

            $parameters = array(':I_Order', ':I_CategoryID');
            $values = array($this->getOrder(), $this->getCategoryID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);


            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $item = new Items();
                    $item->setID($row->I_ID);
                    $item->setCode($row->I_Code);
                    $item->setAlias($row->I_Alias);
                    $item->setName($row->I_Title);
                    $item->setDescription($row->I_Description);
                    $item->setDetail($row->I_Detail);
                    $item->setImage($row->I_Image);
                    $item->setCategoryID($row->I_CategoryID);
                    $item->setPrice($row->I_Price);
                    $item->setCreatedDate($row->I_CreatedDate);
                    $item->setModifiedDate($row->I_ModifiedDate);
                    $item->setCreatedUserID($row->I_CreatedUser);
                    $item->setModifiedUserID($row->I_ModifiedUser);
                    $item->setState($row->I_State);
                    $item->setOrder($row->I_Order);

                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($item->getCreatedUserID());
                    $createdUser->getByID($db);
                    $item->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($item->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $item->setModifiedUser($modifiedUser);


                    // category 
                    $category = new Categories();
                    $category->setID($item->getCategoryID());
                    $category = $category->getByID($db);
                    $item->setCategory($category);

                    $rows[] = $item;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // get an item detail
    public function getByID($db) {
        if ($db != null) {
            $sql = "SELECT 
                    `I_ID`,
                    `I_Code`,
                    `I_Alias`,
                    `I_Title`,
                    `I_Description`,
                    `I_Detail`,
                    `I_Image`,
                    `I_CategoryID`,
                    `I_Price`,
                    Date_FORMAT(I_CreatedDate, '%d/%m/%Y') As I_CreatedDate,
                    Date_FORMAT(I_ModifiedDate, '%d/%m/%Y') As I_ModifiedDate, 
                    `I_CreatedUser`,
                    `I_ModifiedUser`,
                    `I_State`,
                    `I_Order`
                    FROM items
                    WHERE I_ID = :I_ID
                    ORDER BY I_Order DESC";

            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {
                $row = $result->fetch();
                $item = new Items();
                $item->setID($row->I_ID);
                $item->setCode($row->I_Code);
                $item->setAlias($row->I_Alias);
                $item->setName($row->I_Title);
                $item->setDescription($row->I_Description);
                $item->setDetail($row->I_Detail);
                $item->setImage($row->I_Image);
                $item->setCategoryID($row->I_CategoryID);
                $item->setPrice($row->I_Price);
                $item->setCreatedDate($row->I_CreatedDate);
                $item->setModifiedDate($row->I_ModifiedDate);
                $item->setCreatedUserID($row->I_CreatedUser);
                $item->setModifiedUserID($row->I_ModifiedUser);
                $item->setState($row->I_State);
                $item->setOrder($row->I_Order);

                // created user
                $createdUser = new Users();
                $createdUser->setID($item->getCreatedUserID());
                $createdUser->getByID($db);
                $item->setCreatedUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($item->getModifiedUserID());
                $modifiedUser->getByID($db);
                $item->setModifiedUser($modifiedUser);


                // category 
                $category = new Categories();
                $category->setID($item->getCategoryID());
                $category = $category->getByID($db);
                $item->setCategory($category);

                return $item;
            } else {
                return null;
            }
        }
    }

    // get an item detail
    public function getByAlias($db) {
        if ($db != null) {
            $sql = "SELECT 
                    `I_ID`,
                    `I_Code`,
                    `I_Alias`,
                    `I_Title`,
                    `I_Description`,
                    `I_Detail`,
                    `I_Image`,
                    `I_CategoryID`,
                    `I_Price`,
                    Date_FORMAT(I_CreatedDate, '%d/%m/%Y') As I_CreatedDate,
                    Date_FORMAT(I_ModifiedDate, '%d/%m/%Y') As I_ModifiedDate, 
                    `I_CreatedUser`,
                    `I_ModifiedUser`,
                    `I_State`,
                    `I_Order`
                    FROM items
                    WHERE I_Alias = :I_Alias
                    ORDER BY I_Order DESC";

            $parameters = array(':I_Alias');
            $values = array($this->getAlias());
            $types = array(PDO::PARAM_STR);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {
                $row = $result->fetch();
                $item = new Items();
                $item->setID($row->I_ID);
                $item->setCode($row->I_Code);
                $item->setAlias($row->I_Alias);
                $item->setName($row->I_Title);
                $item->setDescription($row->I_Description);
                $item->setDetail($row->I_Detail);
                $item->setImage($row->I_Image);
                $item->setCategoryID($row->I_CategoryID);
                $item->setPrice($row->I_Price);
                $item->setCreatedDate($row->I_CreatedDate);
                $item->setModifiedDate($row->I_ModifiedDate);
                $item->setCreatedUserID($row->I_CreatedUser);
                $item->setModifiedUserID($row->I_ModifiedUser);
                $item->setState($row->I_State);
                $item->setOrder($row->I_Order);

                // created user
                $createdUser = new Users();
                $createdUser->setID($item->getCreatedUserID());
                $createdUser->getByID($db);
                $item->setCreatedUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($item->getModifiedUserID());
                $modifiedUser->getByID($db);
                $item->setModifiedUser($modifiedUser);


                // category 
                $category = new Categories();
                $category->setID($item->getCategoryID());
                $category = $category->getByID($db);
                $item->setCategory($category);

                return $item;
            } else {
                return null;
            }
        }
    }

    // hàm cập nhật giá trị trạng thái
    public function updateState($db) {
        if ($db != null) {
            $sql = "UPDATE `items`
                    SET
                      `I_State` = :I_State
                    WHERE `I_ID` = :I_ID;";
            $parameters = array(':I_State', ':I_ID');
            $values = array($this->getState(), $this->getID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // add a new item into database
    public function add($db) {
        if ($db != null) {
            $sql = "INSERT INTO `items`
                                (
                                 `I_Code`,
                                 `I_Alias`,
                                 `I_Title`,
                                 `I_Description`,
                                 `I_Detail`,
                                 `I_Image`,
                                 `I_CategoryID`,
                                 `I_Price`,
                                 `I_CreatedDate`,
                                 `I_ModifiedDate`,
                                 `I_CreatedUser`,
                                 `I_ModifiedUser`,
                                 `I_State`,
                                 `I_Order`)
                    VALUES (
                            :I_Code,
                            :I_Alias,
                            :I_Title,
                            :I_Description,
                            :I_Detail,
                            :I_Image,
                            :I_CategoryID,
                            :I_Price,
                            :I_CreatedDate,
                            :I_ModifiedDate,
                            :I_CreatedUser,
                            :I_ModifiedUser,
                            :I_State,
                            :I_Order);";
            $parameters = array(
                ":I_Code", ":I_Alias",
                ":I_Title", ":I_Description",
                ":I_Detail", ":I_Image",
                ":I_CategoryID", ":I_Price",
                ":I_CreatedDate", ":I_ModifiedDate",
                ":I_CreatedUser", ":I_ModifiedUser",
                ":I_State", ":I_Order"
            );
            $values = array(
                $this->getCode(), $this->getAlias(),
                $this->getName(), $this->getDescription(),
                $this->getDetail(), $this->getImage(),
                $this->getCategoryID(), $this->getPrice(),
                $this->getCreatedDate(), $this->getModifiedDate(),
                $this->getCreatedUserID(), $this->getModifiedUserID(),
                $this->getState(), $this->getOrder()
            );
            $types = array(
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types, true);
        }
    }

    // delete a new item from database
    public function delete($db) {
        if ($db != null) {
            $sql = "DELETE
                    FROM `items`
                    WHERE `I_ID` = :I_ID;";

            $parameters = array(":I_ID");

            $values = array($this->getID());

            $types = array(PDO::PARAM_INT);

            return $db->executeNonQuery($sql, $parameters, $values, $types, true);
        }
    }

    // update a new item from database
    public function update($db) {
        if ($db != null) {
            $sql = "UPDATE `items`
                    SET `I_ID` = :I_ID,
                      `I_Code` = :I_Code,
                      `I_Alias` = :I_Alias,
                      `I_Title` = :I_Title,
                      `I_Description` = :I_Description,
                      `I_Detail` = :I_Detail,
                      `I_Image` = :I_Image,
                      `I_CategoryID` = :I_CategoryID,
                      `I_Price` = :I_Price,
                      `I_ModifiedDate` = :I_ModifiedDate,
                      `I_ModifiedUser` = :I_ModifiedUser,
                      `I_State` = :I_State
                    WHERE `I_ID` = :I_ID;";

            $parameters = array(":I_ID",
                ":I_Code", ":I_Alias",
                ":I_Title", ":I_Description",
                ":I_Detail", ":I_Image",
                ":I_CategoryID", ":I_Price",
                ":I_ModifiedDate", ":I_ModifiedUser",
                ":I_State"
            );

            $values = array($this->getID(),
                $this->getCode(), $this->getAlias(),
                $this->getName(), $this->getDescription(),
                $this->getDetail(), $this->getImage(),
                $this->getCategoryID(), $this->getPrice(),
                $this->getModifiedDate(), $this->getModifiedUserID(),
                $this->getState()
            );

            $types = array(PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types, true);
        }
    }

    // hàm lấy thứ tự cao nhất
    public function getMaxOrder($db) {
        if ($db != null) {
            $sql = "select MAX(`I_Order`)+1 As `MaxOrder` from `items`;";
            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            $row = $result->fetch();
            if ($row->MaxOrder != null) {
                return $row->MaxOrder;
            } else {
                return 1;
            }
        }
    }

    // ham su ly di chuyen mau tin xuong phia duoi cua news
    public function orderUp($db) {
        if ($db != null) {


            // câu lệnh tải currentOrder, nextOrder, nextID
            $sql = "SELECT (
                    /* lay ra order hien tai */
                    SELECT I_Order FROM items WHERE I_ID = :I_ID) AS currentOrder, 
                            /* lay ra order cao hon */
                            (SELECT I_Order FROM items WHERE I_Order > 
                            (SELECT I_Order FROM items WHERE I_ID = :I_ID) 
                            ORDER BY I_Order LIMIT 1) AS nextOrder,

                            /* lay ra itemID co cai order cao hon */
                            (SELECT I_ID FROM items WHERE I_Order = 
                            (SELECT I_Order FROM items WHERE I_Order > 
                            (SELECT I_Order FROM items WHERE I_ID = :I_ID) 
                            ORDER BY I_Order LIMIT 1)) AS nextItemID";
            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);



            if ($result != null) {
                $row = $result->fetch();

                // cập nhật order của previous item bằng với currentOrder
                $sqlNextArticle = "update items set `I_Order` = :I_Order where `I_ID` = :I_ID";
                $parameters2 = array(':I_Order', ':I_ID');
                $values2 = array($row->currentOrder, $row->nextItemID);
                $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlNextArticle, $parameters2, $values2, $types2);

                // cập nhật order của current item bằng với previous order
                $sqlCurrentArticle = "update items set `I_Order` = :I_Order where `I_ID` = :I_ID";
                $parameters3 = array(':I_Order', ':I_ID');
                $values3 = array($row->nextOrder, $this->getID());
                $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
            }
        }
    }

    function orderDown($db) {

        if ($db != null) {

            // câu lệnh tải currentOrder, previousOrder, previousArticleID
            $sql = "SELECT 
                    (SELECT I_Order FROM items WHERE I_ID = :I_ID) AS currenOrder, 
                
                    (SELECT I_Order FROM items WHERE I_Order < 
                    (SELECT I_Order FROM items WHERE I_ID = :I_ID) 
                    ORDER BY I_Order DESC LIMIT 1) AS previousOrder,

                    (SELECT I_ID FROM items WHERE I_Order = 
                    (SELECT I_Order FROM items WHERE I_Order < 
                    (SELECT I_Order FROM items WHERE I_ID = :I_ID) 
                    ORDER BY I_Order DESC LIMIT 1)) AS previousItemID";

            $parameters = array(':I_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {

                $row = $result->fetch();

                // cập nhật order của previous article bằng với currentOrder
                $sqlPreviousItem = "update items set `I_Order` = :I_Order where `I_ID` = :I_ID";
                $parameters2 = array(':I_Order', ':I_ID');
                $values2 = array($row->currenOrder, $row->previousItemID);
                $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlPreviousItem, $parameters2, $values2, $types2);

                // cập nhật order của current article bằng với previous order
                $sqlCurrentItem = "update items set `I_Order` = :I_Order where `I_ID` = :I_ID";
                $parameters3 = array(':I_Order', ':I_ID');
                $values3 = array($row->previousOrder, $this->getID());
                $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlCurrentItem, $parameters3, $values3, $types3);
            }
        }
    }

}

?>
